package com.df.springmvc.bi.map.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLResultBean;
import com.df.springmvc.supper.SupperDao;

/**
 * 读取数据库的操作
 * @author David
 */
public class mapDao extends SupperDao{
	private String message;

	/**
	 * 获取省级中的市级地图统计信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getshengji(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String sql="SELECT T1.CITY_NAME AS name,sum(case when T1.WGS is not null then T1.WGS else 0 end ) AS value,T1.CITY_SORT as CITY_SORT"
				+" FROM "
				+" (SELECT DISTINCT APP_CITY_UNIT.CITY_NAME,sum(RCJG_SPSC.WFWGSC) AS WGS ,APP_CITY_UNIT.CITY_SORT"
				+" FROM RCJG_SPSC RIGHT JOIN APP_CITY_UNIT ON RCJG_SPSC.CITY_ID=APP_CITY_UNIT.CITY_ID" 
				+" LEFT JOIN  TIME ON TIME.TIME_ID=RCJG_SPSC.TIME_ID"
				+"  WHERE "
				+" APP_CITY_UNIT.CITY_PROVINCE_ID=1"
				//+" AND TIME.YEAR=2016 "
				+"  GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_NAME,APP_CITY_UNIT.CITY_SORT "
				+"  UNION ALL "
				+"  (SELECT DISTINCT APP_CITY_UNIT.CITY_NAME,sum(RCJG_SPJY.WFWGJY) AS WGS ,APP_CITY_UNIT.CITY_SORT"
				+"  FROM RCJG_SPJY RIGHT JOIN APP_CITY_UNIT ON RCJG_SPJY.CITY_ID=APP_CITY_UNIT.CITY_ID " 
				+" LEFT JOIN TIME ON TIME.TIME_ID=RCJG_SPJY.TIME_ID"
				+"  WHERE  " 
				+" APP_CITY_UNIT.CITY_PROVINCE_ID=1"
				//+" AND TIME.YEAR=2016 "
				+"  GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_NAME,APP_CITY_UNIT.CITY_SORT)"
				+" ) T1 "
				+" GROUP BY T1.CITY_NAME,T1.CITY_SORT ";
				
		ArrayList<HashMap<String, Object>> shengjiinfo=querySqlToSQLResultBean(sql).getMapList();
		return shengjiinfo;
	}
	
	/**
	 * 获取市级中的县级地图信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getshiji(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		
		
		String shiji = paramMap.get("shiji");
		
		String sql="SELECT T1.CITY_COUNTY_NAME,sum(case when T1.WGS is not null then T1.WGS else 0 end ) AS wgssum"
                +" FROM "
                +"(SELECT DISTINCT APP_CITY_UNIT.CITY_COUNTY_NAME,sum(RCJG_SPSC.WFWGSC) AS WGS"
                +" FROM RCJG_SPSC RIGHT JOIN APP_CITY_UNIT ON RCJG_SPSC.CITY_ID=APP_CITY_UNIT.CITY_ID" 
                +" LEFT JOIN TIME ON TIME.TIME_ID=RCJG_SPSC.TIME_ID"
                +" WHERE "
                +" APP_CITY_UNIT.CITY_SORT="+shiji
                // +"AND TIME.YEAR=2016 "
                +" GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_COUNTY_NAME "
                +" UNION ALL "
                +" (SELECT DISTINCT APP_CITY_UNIT.CITY_COUNTY_NAME,sum(RCJG_SPJY.WFWGJY) AS WGS "
                +" FROM RCJG_SPJY RIGHT JOIN APP_CITY_UNIT ON RCJG_SPJY.CITY_ID=APP_CITY_UNIT.CITY_ID" 
                +" LEFT JOIN TIME ON TIME.TIME_ID=RCJG_SPJY.TIME_ID"
                +" WHERE " 
                +" APP_CITY_UNIT.CITY_SORT="+shiji
                // +"AND TIME.YEAR=2016 "
                +" GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_COUNTY_NAME)"
                +") t1 "
                +"GROUP BY T1.CITY_COUNTY_NAME ";
		ArrayList<HashMap<String, Object>> jyinfo=querySqlToSQLResultBean(sql).getMapList();
		return jyinfo;
	}

	/**
	 * 获取行政审批生产地图信息
	 * @param paramMap
	 * @return
	 * @throws Exception 
	 */
	public ArrayList<HashMap<String, Object>> XZSP_SC_getmapinfo(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String sqls1="";
		String StartTime = paramMap.get("StartTime");//2016-01
		//拼接时间查询条件
		if(StartTime != null && !"".equals(StartTime)){
			sqls1=sqls1+"   AND T.TIME_ID='"+StartTime+"'";
		}
		String sql="SELECT SUM(SY_HZQYS) AS VALUE,CITY_ID,CITY_NAME FROM( " +
				"SELECT substr(SC.CITY_ID,0,3) AS CITY_ID,CI.CITY_NAME,SC.SY_HZQYS " +
				"FROM XZSP_SPSCXKZ SC  " +
				"LEFT JOIN APP_CITY_UNIT CI ON CI.CITY_ID=SC.CITY_ID " +
				"LEFT JOIN TIME T ON T.TIME_ID=SC.TIME_ID " +
				"WHERE 1=1 "+sqls1+" )  " +
				"GROUP BY CITY_ID,CITY_NAME";
		ArrayList<HashMap<String, Object>> shengjiinfo=querySqlToSQLResultBean(sql).getMapList();
		return shengjiinfo;
	}

	/**
	 * 获取行政审批生产市信息
	 * @param paramMap
	 * @return
	 * @throws Exception 
	 */
	public ArrayList<HashMap<String, Object>> XZSP_SC_getshimapinfo(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		String shiji = paramMap.get("shiji");
		String sqls1="";
		String StartTime = paramMap.get("StartTime");//2016-01
		//拼接时间查询条件
		if(StartTime != null && !"".equals(StartTime)){
			sqls1=sqls1+"   AND T.TIME_ID='"+StartTime+"'";
		}
		String sql="SELECT SUM(SY_HZQYS) AS VALUE,CITY_ID,CITY_NAME FROM( " +
				"SELECT SC.CITY_ID,CI.CITY_COUNTY_NAME AS CITY_NAME,SC.SY_HZQYS " +
				"FROM XZSP_SPSCXKZ SC  " +
				"LEFT JOIN APP_CITY_UNIT CI ON CI.CITY_ID=SC.CITY_ID " +
				"LEFT JOIN TIME T ON T.TIME_ID=SC.TIME_ID " +
				"WHERE 1=1  AND  substr(SC.CITY_ID,0,3)='"+shiji+"' "+sqls1+" )  " +
				"GROUP BY CITY_ID,CITY_NAME";
				
		ArrayList<HashMap<String, Object>> shengjiinfo=querySqlToSQLResultBean(sql,new Object[]{}).getMapList();
		return shengjiinfo;
	}
	
	/**
	 * 获取行政审批经营省级中的市级地图统计信息
	 * @return
	 * @throws Exception
	 */
	
	
	public ArrayList<HashMap<String, Object>> getjyshengji(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
	String sql=" SELECT T1.CITY_NAME AS name,"
			+" sum(case when T1.WGS is not null then T1.WGS else 0 end ) AS value,"
        	+" T1.CITY_SORT as CITY_SORT "
        	+" FROM "
     		+" (SELECT DISTINCT APP_CITY_UNIT.CITY_NAME,sum(XZSP_SPJYXKZ.SY_HZQYS) AS WGS ,APP_CITY_UNIT.CITY_SORT " 
      		+" FROM "
      		+" XZSP_SPJYXKZ RIGHT JOIN APP_CITY_UNIT ON XZSP_SPJYXKZ.CITY_ID=APP_CITY_UNIT.CITY_ID"
       		+" LEFT JOIN TIME ON TIME.TIME_ID=XZSP_SPJYXKZ.TIME_ID"
       		+" WHERE APP_CITY_UNIT.CITY_PROVINCE_ID=1  "
       		//+"AND TIME.YEAR=2016"
       		+" GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_NAME,APP_CITY_UNIT.CITY_SORT"
       		+"  )  T1  "
    		+" GROUP BY "
     		+"  T1.CITY_NAME,T1.CITY_SORT ";
		ArrayList<HashMap<String, Object>> shengjiinfo=querySqlToSQLResultBean(sql).getMapList();
		return shengjiinfo;
	}
	
	/**
	 * 获取行政审批经营市级中的县级地图信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getjyshiji(HashMap<String, String> paramMap) throws Exception {
		// TODO Auto-generated method stub
		
		
		String shiji = paramMap.get("shiji");
		
		String sql="SELECT T1.CITY_COUNTY_NAME,sum(case when T1.WGS is not null then T1.WGS else 0 end ) AS YX_XKZ"
                +" FROM "
                +" (SELECT DISTINCT APP_CITY_UNIT.CITY_COUNTY_NAME,sum(XZSP_SPJYXKZ.SY_HZQYS) AS WGS "
                +" FROM XZSP_SPJYXKZ RIGHT JOIN APP_CITY_UNIT ON XZSP_SPJYXKZ.CITY_ID=APP_CITY_UNIT.CITY_ID"
                +" LEFT JOIN TIME  ON TIME.TIME_ID=XZSP_SPJYXKZ.TIME_ID"
                +" WHERE APP_CITY_UNIT.CITY_SORT="+shiji
                //+" AND TIME.YEAR=2016"
                +" GROUP BY TIME.TIME_ID,APP_CITY_UNIT.CITY_COUNTY_NAME)"
                +" T1 "
                +"GROUP BY T1.CITY_COUNTY_NAME ";
		ArrayList<HashMap<String, Object>> jyinfo=querySqlToSQLResultBean(sql).getMapList();
		return jyinfo;
	}
	
}
